package vcp.server.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.joda.time.DateTime;

import vcp.entity.Car;
import vcp.entity.Lot;
import vcp.entity.Subscription;
import vcp.enums.SiteEnum;
import vcp.server.DBAdapter;
import vcp.server.DAO.interfaces.IGenericDAO;
import vcp.server.utility.Constants.CarsTable;
import vcp.server.utility.Constants.SubscribtionsTable;
import vcp.server.utility.Constants.TableNames;
import vcp.server.utility.SQLQueries;

/**
 * <p>
 * car DAO class , implements relevant sql queries
 * </p>
 * 
 * @author VCP team
 * 
 */
public class CarDAO implements IGenericDAO <Car, Subscription>
{
	/**
	 * Calculate number of business class customers
	 * 
	 * @return number of business class customers
	 */
	public int findTotalNumOfBussinessClass ( SiteEnum siteEnum )
	{
		int countTotal = 0;
		int countDistinct = 0;
		int countDelta = 0;

		String query =
				SQLQueries.SELECT
						+ " " + CarsTable.SUBSCRIPTION_ID
						+ " " + SQLQueries.FROM + " " + TableNames.CARS
						+ " " + SQLQueries.WHERE + " " + CarsTable.SUBSCRIPTION_ID + " "
						+ "in (" + SQLQueries.SELECT + " " + SubscribtionsTable.ID
						+ " " + SQLQueries.FROM + " " + TableNames.SUBSCRIPTION
						+ " " + SQLQueries.WHERE + " " + SubscribtionsTable.SITE_NAME + "=" + "\"" + siteEnum.toString() + "\"" + ")";

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			while ( set.next() )
			{
				countTotal ++ ;
			}

			query =
					SQLQueries.SELECT
							+ " " + "distinct(" + CarsTable.SUBSCRIPTION_ID + ")"
							+ " " + SQLQueries.FROM + " " + TableNames.CARS
							+ " " + SQLQueries.WHERE + " " + CarsTable.SUBSCRIPTION_ID
							+ " in (" + SQLQueries.SELECT + " " + SubscribtionsTable.ID
							+ " " + SQLQueries.FROM + " " + TableNames.SUBSCRIPTION
							+ " " + SQLQueries.WHERE + " " + SubscribtionsTable.SITE_NAME + "=" + "\"" + siteEnum.toString() + "\"" + ")";

			try
			{
				set = DBAdapter.getAdapter().find(query);
				while ( set.next() )
				{
					countDistinct ++ ;
				}

				countDelta = countTotal - countDistinct;
			}
			catch ( SQLException e )
			{
				e.printStackTrace();
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return countDelta;
	}

	/**
	 * updates car status to be parked on site
	 * 
	 * @param ID
	 * @param isParked
	 * @return true if success
	 */
	public boolean updateParkedOnSite ( int ID, boolean isParked )
	{
		String sqlQuery =
				String.format(
					SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
					, TableNames.CARS
					, CarsTable.IS_PARKED_ON_SITE + "=" + isParked
					, CarsTable.ID + "=" + String.valueOf(ID));

		boolean success = this.refresh(sqlQuery);
		return success;
	}

	/**
	 * updates arrival time per car Id
	 * 
	 * @param ID
	 * @param arriveTime
	 * @return true if success
	 */
	public boolean updateArrivalTime ( int ID, DateTime arriveTime )
	{
		String sqlQuery =
				String.format(
					SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
					, TableNames.CARS
					, CarsTable.ARRIVED_TIME + "=" + "\"" + new Timestamp(arriveTime.getMillis()) + "\""
					, CarsTable.ID + "=" + String.valueOf(ID));

		boolean success = this.refresh(sqlQuery);
		return success;

	}

	/**
	 * updates last check out date for car
	 * 
	 * @param ID
	 * @param checkoutDate
	 * @return true if success
	 */
	public boolean updateLastCheckOutDate ( int ID, DateTime checkoutDate )
	{
		String sqlQuery =
				String.format(
					SQLQueries.UPDATE__TABLENAME__SET__SINGLEVALUE__WHERE__COMPARE_
					, TableNames.CARS
					, CarsTable.LAST_CHECK_OUT_DATE + "=" + "\"" + new Timestamp(checkoutDate.getMillis()) + "\""
					, CarsTable.ID + "=" + String.valueOf(ID));

		boolean success = this.refresh(sqlQuery);
		return success;

	}

	/****************************************************************************************************************************
	 *** IGenericDAO methods ***
	 ****************************************************************************************************************************/

	/**
	 * Not implemented
	 */
	@Override
	public Integer findMaxID ()
	{
		return null;
	}

	@Override
	public Car create ()
	{
		return new Car();
	}

	/**
	 * Not implemented
	 */
	@Override
	public Car findByEntity ( Subscription entity )
	{
		LotDAO lotDAO = new LotDAO();
		Lot lot = lotDAO.findByReservedSubscriptionID(entity.getSubscribtionID());

		Car car = null;
		if ( lot != null )
		{
			car = this.findByID(lot.getCarID());
		}
		return car;
	}

	@Override
	public Car findByID ( int ID )
	{
		return this.findByID(String.valueOf(ID));
	}

	@Override
	public Car findByID ( String ID )
	{
		String query =
				String.format(
					SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
					, TableNames.CARS
					, CarsTable.ID + "=" + ID);

		Car car = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				car = this.create();

				car.setCarID(set.getInt(CarsTable.ID));
				car.setSubscriptionID(set.getInt(CarsTable.SUBSCRIPTION_ID));
				car.setCustomerID(set.getInt(CarsTable.CUSTOMER_ID));
				car.setParkedOnSite(set.getBoolean(CarsTable.IS_PARKED_ON_SITE));
				car.setArrivedTime(new DateTime(set.getTimestamp(CarsTable.ARRIVED_TIME)));
				car.setLastCheckOutDate(new DateTime(set.getTimestamp(CarsTable.LAST_CHECK_OUT_DATE)));
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return car;
	}

	@Override
	public List <Car> findAll ( Subscription info )
	{
		String query =
				String.format(
					SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
					, TableNames.CARS
					, CarsTable.SUBSCRIPTION_ID + "=" + String.valueOf(info.getSubscribtionID()))
						+ " " + SQLQueries.AND
						+ CarsTable.CUSTOMER_ID + "=" + String.valueOf(info.getCustomerID());

		List <Car> listOfCars = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			listOfCars = new ArrayList <Car>();
			while ( set.next() )
			{
				Car car = this.create();

				car.setCarID(set.getInt(CarsTable.ID));
				car.setCustomerID(set.getInt(CarsTable.CUSTOMER_ID));
				car.setSubscriptionID(info.getSubscribtionID());
				car.setParkedOnSite(set.getBoolean(CarsTable.IS_PARKED_ON_SITE));
				car.setArrivedTime(new DateTime(set.getTimestamp(CarsTable.ARRIVED_TIME)));
				car.setLastCheckOutDate(new DateTime(set.getTimestamp(CarsTable.LAST_CHECK_OUT_DATE)));

				listOfCars.add(car);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return listOfCars;
	}

	@Override
	public boolean save ( Car entity )
	{
		boolean result = false;
		String valuesSet =
				String.format(
					"%d,%d,%d,%d,\"%s\",\"%s\""
					, entity.getCarID()
					, entity.getSubscriptionID()
					, entity.getCustomerID()
					, entity.isParkedOnSite()
												? 1
												: 0
					, entity.getArrivedTime() != null
														? new Timestamp(entity.getArrivedTime().getMillis())
														: new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis())
					, entity.getLastCheckOutDate() != null
															? new Timestamp(entity.getLastCheckOutDate().getMillis())
															: new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis()));

		String sqlQuery =
				String.format(
					SQLQueries.INSERT_INTO__TABLENAME__VALUES__
					, TableNames.CARS
					, valuesSet);

		try
		{
			result = DBAdapter.getAdapter().save(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return result;
	}

	@Override
	public boolean refresh ( String sqlQuery )
	{
		boolean success = false;

		try
		{
			success = DBAdapter.getAdapter().refresh(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}
		return success;
	}

	@Override
	public boolean deleteByID ( int ID )
	{
		return this.deleteByID(String.valueOf(ID));
	}

	@Override
	public boolean deleteByID ( String ID )
	{
		String sqlQuery =
				String.format(
					SQLQueries.DELETE_FROM__TABLENAME__WHERE__COMPARE_
					, TableNames.CARS
					, CarsTable.ID + "=" + ID);

		boolean success = false;
		try
		{
			success = DBAdapter.getAdapter().delete(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return success;
	}

	/**
	 * test queries functionality
	 * 
	 * @param args
	 */
	public static void main ( String[] args )
	{

		System.out.println("******************TESTING CarsDAO***************");
		CarDAO testCarDAO = new CarDAO();
		Car testCar = testCarDAO.create();

		testCar.setCarID(3978957);
		testCar.setSubscriptionID(8);
		testCar.setCustomerID(717656763);
		testCar.setParkedOnSite(true);
		testCar.setArrivedTime(null);
		testCar.setLastCheckOutDate(new DateTime().minusDays(3));
		testCarDAO.save(testCar);
		System.out.println("Car 1 was saved");
		System.out.println("*********************************************************");
		Car testCar1 = testCarDAO.findByID(8664429);
		System.out.println("Arrival time" + testCar1.getArrivedTime());

		testCar1.setCarID(2562533);
		testCar1.setSubscriptionID(1234);
		testCar1.setCustomerID(33278938);
		testCar1.setParkedOnSite(true);
		testCar1.setArrivedTime(new DateTime());
		testCar1.setLastCheckOutDate(new DateTime().minusDays(5));
		testCarDAO.save(testCar1);
		System.out.println("Car 2 was saved");
		System.out.println("*********************************************************");

		testCar = testCarDAO.findByID(8664429);
		System.out.println("User with id:" + testCar.getCarID() +
				" was found");
		System.out.println("*********************************************************");

		Subscription sub = new Subscription();
		sub.setSubscribtionID(1234);
		for ( Car car : testCarDAO.findAll(sub) )
		{
			System.out.println("carID:" + car.getCarID());
		}
		System.out.println("*********************************************************");

		testCarDAO.updateParkedOnSite(testCar.getCarID(), false);
		System.out.println("Car with id:" + testCar.getCarID() +
				" was updated");
		testCarDAO.updateParkedOnSite(testCar1.getCarID(), false);
		System.out.println("Car with id:" + testCar1.getCarID() +
				" was updated");

		System.out.println("*********************************************************");
		testCarDAO.updateLastCheckOutDate(testCar.getCarID(), new DateTime().minusDays(1));
		System.out.println("Checkout Date for Car with id:" + testCar.getCarID() +
				" was updated");

		System.out.println("*********************************************************");
		testCarDAO.updateArrivalTime(testCar.getCarID(), new DateTime().plusDays(40));
		System.out.println("Arrival Time for Car with id:" + testCar.getCarID() +
				" was updated");

		// for (User foundCar : testCarDAO.findAll(info))
		// {
		// System.out.println("User with id:" + foundUser.getUserID() +
		// " was found");
		// }
		//
		// System.out.println("*********************************************************");

		testCarDAO.deleteByID(8664429);
		// testCarDAO.deleteByID(12345678);
		// System.out.println("Car was deleted");
	}

}
